package com.coffee.kaoqin;

import java.io.File;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;

public class ImportExcel {
	static Integer rowNum=0;
	public static File processFile(File file) throws Exception {
		
		POIFSFileSystem poifsFileSystem = new POIFSFileSystem(file);
		DateFormat dateFormat=new SimpleDateFormat("HH:mm");
		HSSFWorkbook workbook = new HSSFWorkbook(poifsFileSystem);
        HSSFSheet sheet = workbook.getSheetAt(0);
        HSSFRow rowDate = sheet.getRow(2);
        HSSFCell cellDate=rowDate.getCell(25);
        String dateString=cellDate.getStringCellValue();
        List<HSSFRow> rows=new ArrayList<HSSFRow>();
        Map<String, List<HSSFRow>> map=new HashMap<String, List<HSSFRow>>();
        String name="";
        for (int i = 4; ; i++) {
        	HSSFRow row = sheet.getRow(i);
        	if(row==null) {
        		if(StringUtils.isNotBlank(name)) {
					map.put(name, rows);
				}
        		break;
        	}
        	HSSFCell cell=row.getCell(1);
        	if (cell!=null&&cell.getCellType().equals(CellType.STRING)) {
				if(cell.getStringCellValue().contains("工号")) {
					if(StringUtils.isNotBlank(name)) {
						map.put(name, rows);
						rows=new ArrayList<HSSFRow>();
					}
					HSSFCell nameCell=row.getCell(11);
					HSSFCell noCell=row.getCell(3);
					name=noCell.getStringCellValue()+":"+nameCell.getStringCellValue();
					i++;
				}else {
					rows.add(row);
				}
			}else {
				rows.add(row);
			}
        }
        
        File output=new File(file.getParentFile()+"\\整理_"+file.getName());
        HSSFWorkbook outWorkbook = new HSSFWorkbook();
		//创建Excel对象
		HSSFSheet outSsheet = outWorkbook.createSheet("打卡统计"); 
		
		//在sheet中添加表头第0行
		HSSFRow outRow = outSsheet.createRow(rowNum); 
		rowNum++;
		//创建单元格，并设置值表头 设置表头居中  
		HSSFCellStyle style = outWorkbook.createCellStyle();  
		style.setAlignment(HorizontalAlignment.CENTER);
        HSSFCell outCell=outRow.createCell(0);
        outCell.setCellValue(dateString);
        outCell.setCellStyle(style);
        map.forEach((key, value) -> {
        	Map<Integer,Long> temp=new HashMap<>();
        	value.forEach(row ->{
        		for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
					HSSFCell cell=row.getCell(i);
					Long a=temp.get(i);
					if(a==null) {
						a=0L;
					}
					if(a==-1) {
						continue;
					}
					
					if (cell!=null) {
						if (StringUtils.isNotBlank(cell.getStringCellValue())) {
							String start=cell.getStringCellValue().split("\n")[0];
		    				String end=cell.getStringCellValue().split("\n")[1];
		    				if(StringUtils.isNotBlank(start)&&StringUtils.isNotBlank(end)) {
								try {
									Date startDate = dateFormat.parse(start);
									Date endDate=dateFormat.parse(end);
									long c =  Math.abs( endDate.getTime() - startDate.getTime()) / (1000 * 60);
									a+=c;
								} catch (ParseException e) {
									a=-1L;
									e.printStackTrace();
								}
		    				}else {
		    					a=-1L;
		    				}
						}
					}
					temp.put(i, a);
				}
        		
        	});
        	HSSFRow outRow1 = outSsheet.createRow(rowNum);
        	outRow1.createCell(0).setCellValue("工号：");
        	outRow1.createCell(1).setCellValue(key.split(":")[0]);
        	outRow1.createCell(2).setCellValue("姓名：");
			if (key.split(":").length>1) {
				outRow1.createCell(3).setCellValue(key.split(":")[1]);

			}else{
				outRow1.createCell(3).setCellValue("未知");
			}
        	rowNum++;
        	HSSFRow outRow2 = outSsheet.createRow(rowNum);
        	
        	rowNum++;
        	HSSFRow outRow3 = outSsheet.createRow(rowNum);
        	temp.forEach((intKey,longValue) ->{
        		if(longValue==-1) {
        			//设置标题字体
        			Font fontTitle = outWorkbook.createFont();
        			fontTitle.setColor(IndexedColors.RED.getIndex()); //字体颜色
        			CellStyle cellStyle=outWorkbook.createCellStyle();
        			cellStyle.setFont(fontTitle);
        			HSSFCell outCell2=outRow2.createCell(intKey-1);
        			outCell2.setCellValue(intKey+"号");
        			outCell2.setCellStyle(cellStyle);
        			HSSFCell outCell3=outRow3.createCell(intKey-1);
        			outCell3.setCellValue(0);
        			outCell3.setCellStyle(cellStyle);
        		}else {
        			HSSFCell outCell2=outRow2.createCell(intKey-1);
        			outCell2.setCellValue(intKey+"号");
        			HSSFCell outCell3=outRow3.createCell(intKey-1);
        			outCell3.setCellValue(longValue);
        		}
        	});
        	outRow2.createCell(temp.size()).setCellValue("合计");;
        	HSSFCell formulaCell=outRow3.createCell(temp.size());
        	String start=CellReference.convertNumToColString(0);
        	String end=CellReference.convertNumToColString(temp.size()-1);
        	rowNum++;
        	formulaCell.setCellFormula( "SUM(" + start +rowNum+":" + end + rowNum + ")");
        });
        outWorkbook.write(output);
        outWorkbook.close();
        return output;
	}
}
